import plotly.graph_objs as go
from plotly.subplots import make_subplots
import pandas as pd
import numpy as np
import os
from pathlib import Path

# 设置时间粒度 - 可选择 'hourly', 'daily' 或 'monthly'
# TIME_GRANULARITY = input("设置时间粒度 - 可选择 'hourly', 'daily' 或 'monthly':")  # 修改此处切换时间粒度
# SELECTED_DEV_LINE = input("请输入车型DEV_LINE: ")  # 新增车型输入

TIME_GRANULARITY = ''
SELECTED_DEV_LINE = ''

data_dir = os.path.dirname(os.path.abspath(__file__)) + '/query_data/'
out_html_path = Path(__file__).resolve().parent.parent / "templates" / "command_success_analysis.html"

# 从Excel文件读取数据
def load_data():
    try:

        # 读取Excel文件
        df = pd.read_excel(data_dir + "query_data.xlsx")

        # 数据预处理
        df['hour'] = pd.to_datetime(df['hour'], errors='coerce')  # 更安全的日期转换

        # 检查并处理可能的空值
        df['ERR_DESCRIPTION'] = df['ERR_DESCRIPTION'].fillna('未知错误').astype(str)

        # 定义成功指令的关键词
        success_keywords = [
            "指令执行成功", "执行成功", "空调开启成功",
            "指令开启成功", "远程座椅通风开启成功", "远程座椅加热开启成功"
        ]

        # 标记成功指令 - 更安全的处理方式
        def is_success(desc):
            if not isinstance(desc, str):  # 确保是字符串
                return 0
            return 1 if any(keyword in desc for keyword in success_keywords) else 0

        df['is_success'] = df['ERR_DESCRIPTION'].apply(is_success)

        return df

    except Exception as e:
        print(f"数据加载错误: {e}")
        # 创建空DataFrame保持结构
        return pd.DataFrame(columns=['hour', 'DEV_LINE', 'ERR_DESCRIPTION', 'cmd_count'])


# 计算按时间粒度的成功率（针对特定车型）
def calculate_temporal_success(df, granularity, dev_line):
    if df.empty or 'hour' not in df.columns:
        return pd.DataFrame(columns=['time_period', 'total_cmds', 'success_cmds', 'success_rate'])

    # 确保有有效的时间数据
    df = df.dropna(subset=['hour'])

    # 过滤特定车型的数据
    if dev_line:
        df = df[df['DEV_LINE'] == dev_line]

    # 根据选择的时间粒度进行处理
    if granularity == 'hourly':
        # 按小时分组
        temporal = df.groupby(pd.Grouper(key='hour', freq='h')).agg(
            total_cmds=('cmd_count', 'sum'),
            success_cmds=('is_success', lambda x: (x * df.loc[x.index, 'cmd_count']).sum())
        ).reset_index()
        temporal = temporal.rename(columns={'hour': 'time_period'})
        temporal['time_label'] = temporal['time_period'].dt.strftime('%Y-%m-%d %H:00')
        chart_title = f"按小时指令成功率分析（车型：{dev_line}）" if dev_line else "按小时指令成功率分析"
        xaxis_title = "时间"

    elif granularity == 'daily':
        # 按天分组
        temporal = df.groupby(pd.Grouper(key='hour', freq='D')).agg(
            total_cmds=('cmd_count', 'sum'),
            success_cmds=('is_success', lambda x: (x * df.loc[x.index, 'cmd_count']).sum())
        ).reset_index()
        temporal = temporal.rename(columns={'hour': 'time_period'})
        temporal['time_label'] = temporal['time_period'].dt.strftime('%Y-%m-%d')
        chart_title = f"按天指令成功率分析（车型：{dev_line}）" if dev_line else "按天指令成功率分析"
        xaxis_title = "日期"

    elif granularity == 'monthly':
        # 按月分组
        temporal = df.copy()
        temporal['month'] = temporal['hour'].dt.to_period('M').dt.to_timestamp()
        temporal = temporal.groupby('month').agg(
            total_cmds=('cmd_count', 'sum'),
            success_cmds=('is_success', lambda x: (x * temporal.loc[x.index, 'cmd_count']).sum())
        ).reset_index()
        temporal = temporal.rename(columns={'month': 'time_period'})
        temporal['time_label'] = temporal['time_period'].dt.strftime('%Y年%m月')
        chart_title = f"按月指令成功率分析（车型：{dev_line}）" if dev_line else "按月指令成功率分析"
        xaxis_title = "月份"

    else:
        raise ValueError(f"未知的时间粒度: {granularity}. 请选择 'hourly', 'daily' 或 'monthly'")

    # 计算成功率百分比
    temporal['success_rate'] = (temporal['success_cmds'] / temporal['total_cmds'] * 100).round(1)

    return temporal, chart_title, xaxis_title


# 计算按车型的成功率（添加高亮功能）
def calculate_dev_line_success(df, selected_line=None):
    if df.empty:
        return pd.DataFrame(columns=['DEV_LINE', 'total_cmds', 'success_cmds', 'success_rate'])

    # 按车型分组计算总指令数和成功指令数
    dev_line = df.groupby('DEV_LINE').agg(
        total_cmds=('cmd_count', 'sum'),
        success_cmds=('is_success', lambda x: (x * df.loc[x.index, 'cmd_count']).sum())
    ).reset_index()

    # 计算成功率百分比
    dev_line['success_rate'] = (dev_line['success_cmds'] / dev_line['total_cmds'] * 100).round(1)

    # 添加是否高亮标记
    if selected_line:
        dev_line['is_highlighted'] = dev_line['DEV_LINE'] == selected_line
    else:
        dev_line['is_highlighted'] = False

    # 按成功率降序排列
    dev_line = dev_line.sort_values('success_rate', ascending=False)

    return dev_line


# 计算指令执行结果的分布（针对特定车型）
def calculate_error_distribution(df, dev_line):
    if df.empty:
        return pd.DataFrame(columns=['指令执行结果', '命令总数', '百分比'])

    # 过滤特定车型的数据
    if dev_line:
        df = df[df['DEV_LINE'] == dev_line]

    # 按错误描述分组
    error_dist = df.groupby('ERR_DESCRIPTION')['cmd_count'].sum().reset_index()
    error_dist.columns = ['指令执行结果', '命令总数']

    # 计算百分比
    total_cmds = error_dist['命令总数'].sum()
    error_dist['百分比'] = (error_dist['命令总数'] / total_cmds * 100).round(1)

    # 添加成功率信息
    success_cmds = df[df['is_success'] == 1]['cmd_count'].sum()
    total_cmds = df['cmd_count'].sum()
    success_rate = (success_cmds / total_cmds * 100).round(1) if total_cmds > 0 else 0

    # 添加汇总行
    summary_row = pd.DataFrame({
        '指令执行结果': [f'汇总（车型：{dev_line}）'],
        '命令总数': [total_cmds],
        '百分比': [success_rate]
    })

    # 按命令总数降序排列
    error_dist = error_dist.sort_values('命令总数', ascending=False)

    # 合并汇总行
    error_dist = pd.concat([error_dist, summary_row], ignore_index=True)

    return error_dist


# 主程序
def query_by_time_type(search_condition, type):
    TIME_GRANULARITY = search_condition
    SELECTED_DEV_LINE = type
    print("query_by_time_type " + TIME_GRANULARITY + " " + SELECTED_DEV_LINE)
    # 加载数据
    raw_data = load_data()

    # 计算时间粒度的成功率（针对特定车型）
    temporal_success, temporal_title, xaxis_title = calculate_temporal_success(
        raw_data, TIME_GRANULARITY, SELECTED_DEV_LINE
    )

    # 计算其他统计数据
    dev_line_success = calculate_dev_line_success(raw_data, SELECTED_DEV_LINE)
    error_dist = calculate_error_distribution(raw_data, SELECTED_DEV_LINE)

    # 创建子图布局
    fig = make_subplots(
        rows=3, cols=1,
        specs=[
            [{"type": "bar"}],
            [{"type": "bar"}],
            [{"type": "table"}]
        ],
        vertical_spacing=0.1,
        subplot_titles=(
            temporal_title,
            "按车型配置指令成功率分析",
            f"指令执行结果分布（车型：{SELECTED_DEV_LINE}）" if SELECTED_DEV_LINE else "指令执行结果分布"
        ),
        row_heights=[1, 1, 4]
    )

    # 添加柱状图（按时间粒度成功率）- 仅当有数据时
    if not temporal_success.empty:
        # 计算最小成功率（确保不低于0）
        min_rate = max(0, temporal_success['success_rate'].min() - 5)
        max_rate = min(100, temporal_success['success_rate'].max() + 5)

        fig.add_trace(
            go.Bar(
                x=temporal_success['time_label'],
                y=temporal_success['success_rate'],
                marker_color='#1f77b4',
                text=temporal_success['success_rate'].apply(lambda x: f"{x}%" if not pd.isna(x) else "N/A"),
                textposition='outside',
                name=f'{TIME_GRANULARITY}成功率',
                hovertemplate=(
                    '<b>时间</b>: %{x}<br>'
                    '<b>成功率</b>: %{y:.1f}%<br>'
                    '<b>总指令数</b>: %{customdata[0]}<br>'
                    '<b>成功指令数</b>: %{customdata[1]}'
                ),
                customdata=np.stack((
                    temporal_success['total_cmds'],
                    temporal_success['success_cmds']
                ), axis=-1)
            ),
            row=1, col=1
        )

        # 设置y轴范围基于数据最小值
        fig.update_yaxes(
            title_text="成功率 (%)",
            row=1, col=1,
            range=[min_rate, max_rate + (max_rate - min_rate) * 0.1]
        )

    # 添加柱状图（按车型成功率）- 仅当有数据时
    if not dev_line_success.empty:
        # 计算最小成功率（确保不低于0）
        min_rate = max(0, dev_line_success['success_rate'].min() - 5)
        max_rate = min(100, dev_line_success['success_rate'].max() + 5)

        # 创建颜色数组：选定的车型使用原色，其他车型变浅
        colors = ['#ff7f0e' if highlighted else 'lightgray'
                  for highlighted in dev_line_success['is_highlighted']]

        fig.add_trace(
            go.Bar(
                x=dev_line_success['DEV_LINE'],
                y=dev_line_success['success_rate'],
                marker_color=colors,
                text=dev_line_success['success_rate'].apply(lambda x: f"{x}%" if not pd.isna(x) else "N/A"),
                textposition='outside',
                name='车型成功率',
                hovertemplate=(
                    '<b>车型配置</b>: %{x}<br>'
                    '<b>成功率</b>: %{y:.1f}%<br>'
                    '<b>总指令数</b>: %{customdata[0]}<br>'
                    '<b>成功指令数</b>: %{customdata[1]}'
                ),
                customdata=np.stack((
                    dev_line_success['total_cmds'],
                    dev_line_success['success_cmds']
                ), axis=-1)
            ),
            row=2, col=1
        )

        # 设置y轴范围基于数据最小值
        fig.update_yaxes(
            title_text="成功率 (%)",
            row=2, col=1,
            range=[min_rate, max_rate + (max_rate - min_rate) * 0.1]
        )

    # 添加指令执行结果分布表格
    if not error_dist.empty:
        # 创建表格数据
        values = [
            error_dist['指令执行结果'],
            error_dist['命令总数'],
            error_dist['百分比'].apply(lambda x: f"{x}%")
        ]

        # 设置汇总行样式
        fill_color = []
        font_color = []
        for result in error_dist['指令执行结果']:
            if '汇总' in result:
                fill_color.append('rgba(42, 63, 95, 0.7)')  # 深蓝色背景
                font_color.append('white')
            else:
                fill_color.append('#f9f9f9')
                font_color.append('#2a3f5f')

        fig.add_trace(
            go.Table(
                header=dict(
                    values=['指令执行结果', '命令总数', '百分比(%)'],
                    fill_color='#2a3f5f',
                    align='center',
                    font=dict(size=12, color='white', family="Arial"),
                    height=40
                ),
                cells=dict(
                    values=values,
                    fill_color=[fill_color] * 3,
                    align='center',
                    font=dict(size=11, color=[font_color] * 3, family="Arial"),
                    height=30
                ),
                columnwidth=[1.5, 1, 1]  # 调整列宽
            ),
            row=3, col=1
        )

    # 更新布局
    fig.update_layout(
        height=1500,
        title_text=f"指令执行成功率分析报告 ({TIME_GRANULARITY}, 车型: {SELECTED_DEV_LINE or '全部'})",
        title_font=dict(size=24, color='#2a3f5f'),
        hovermode="x unified",
        showlegend=False,
        margin=dict(t=150, b=50, l=50, r=50),
        plot_bgcolor='rgba(240, 240, 240, 0.9)'
    )

    # 更新柱状图样式 - 仅当有数据时
    if not temporal_success.empty:
        fig.update_xaxes(title_text=xaxis_title, row=1, col=1, tickangle=-45)

    if not dev_line_success.empty:
        fig.update_xaxes(title_text="车型配置", row=2, col=1, tickangle=-45)

    # 更新表格标题位置
    fig.update_annotations(font_size=14)

    # 保存为HTML文件
    # output_file = f"command_success_analysis.html"
    output_file = out_html_path


    # 先删除已存在的文件（如果存在）
    if os.path.exists(output_file):
        try:
            fig.write_html(output_file, auto_open=False)
            print(f"已删除原有文件: {output_file}")
        except Exception as e:
            print(f"删除文件时出错: {e}")
            return False

    # 写入新文件
    fig.write_html(output_file, auto_open=False)

    # 判断新文件是否生成
    if os.path.exists(output_file):
        print(f"新html文件写入成功, 分析报告已保存为 {output_file}")
        return True
    else:
        print("新html文件写入失败")
        return False

